/**
 * 
 */
package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import es.ats.codington.festival.domain.Event;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.exceptions.DAOException;
import es.ats.codington.festival.interfaces.daos.IEventDAO;
import es.ats.codington.festival.utils.FERSDataConnection;
import es.ats.codington.festival.utils.FERSDbQuery;

/**
 * @author Codington_RED
 *
 */
public class EventDAOImpl implements IEventDAO { 
	
	private Connection conn;
	public int key;
	/**
	 * Default constructor for EventDAOImpl
	 * @param conn
	 */
	public EventDAOImpl (Connection conn){
		this.conn = conn;
		
		
	}

	/**
	 * Method that retrieves a list with all the events in it.
	 * @param resultSet
	 * @return events 
	 * @throws SQLException
	 */
	private List<Event> getEventList(ResultSet resultSet) throws SQLException {
		
		List<Event> events = new ArrayList<Event>();
		
		while(resultSet.next()) {	      
		    Event event = new Event();
		
		    event.setEventID(resultSet.getInt("eventId"));
		    event.setName(resultSet.getString("name"));
		    event.setDescription(resultSet.getString("description"));
		    event.setPlace(resultSet.getString("place"));
		    event.setStartEvent(resultSet.getString("startEvent"));
		    event.setEndEvent(resultSet.getString("endEvent"));
		    event.setDayEvent(resultSet.getString("dayEvent"));
		    event.setEventType(resultSet.getString("eventType"));
		    event.setSeatsAvailable(resultSet.getInt("seatsAvailable"));
		    event.setIdPlace(resultSet.getInt("idPlace"));
		 
		    events.add(event);
		    
		}
		
		return events;
	}
	/**
	 * This method obtains a resultSet with the Event List
	 * @param resultSet
	 * @return event
	 * @throws SQLException
	 */
	@Override
	 public Event getEvent(ResultSet resultSet) throws SQLException {
			
			Event event = null;	
			List<Event> events = this.getEventList(resultSet);	
			
			if(!events.isEmpty()) {
			    event = events.get(0);
			    
			}
					
			return event;
		    }  
	/**
	 * selects an event by its id and and visitor ID
	 * @param event
	 * @return eventh
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckEvent(Event event) throws DAOException, SQLException {
		boolean eventh = false;
		//Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getCheckEvent();
		ResultSet rs = null;
		
		try{
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, event.getName());
			ps.setString(2, event.getDescription());
			ps.setString(3, event.getPlace());
			ps.setString(4, event.getStartEvent());
			ps.setString(5, event.getEndEvent());
			ps.setString(6, event.getDayEvent());
			ps.setString(7, event.getEventType());
			ps.setInt(8, event.getSeatsAvailable());
			ps.setInt(9, event.getIdPlace());
			//System.out.println(visitor.getUserName());
			//System.out.println( visitor.getPassword());


			
			
			rs = ps.executeQuery();
			//System.out.println(rs.getRow());
			//rs.last();
			
			
			
			}catch(SQLException ex) {
				throw new DAOException(ex.getMessage(), ex);
			}
		
		rs.first();
		if (rs.getRow() == 1) {
			eventh = true;
		}
		
		
		
		return eventh;
	}
	
	/**
	 * retrieve a list with all the events.
	 * @return events
	 * @throws DAOException
	 */
	@Override
	public List<Event> showAllEvents() throws DAOException {
		
		List<Event> events = new ArrayList<Event>();	
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = FERSDbQuery.getAllEvents();	
		
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    
		    resultSet =  preparedStatement.executeQuery();
		    
		    events = this.getEventList(resultSet);
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {
		    FERSDataConnection.closeStatement(preparedStatement);
		    FERSDataConnection.closeResultSet(resultSet);
		    
		}	
			
		return events;	
	}
	
	/**
	 * retrieve a list with all the events and you must pass it the event Name
	 * @param eventname
	 * @return events
	 * @throws DAOException
	 */
	@Override
	public List<Event> showEvents(Event eventname) throws DAOException {
		
		List<Event> events = new ArrayList<Event>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = FERSDbQuery.getSearchByEventName();
			
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    //preparedStatement.setString(1, "%" + eventname.getName() + "%");
		    preparedStatement.setString(1, eventname.getName());
		    
		    resultSet =  preparedStatement.executeQuery();
		    
		    events = this.getEventList(resultSet);
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {
		    FERSDataConnection.closeStatement(preparedStatement);
		    FERSDataConnection.closeResultSet(resultSet);
		    
		}	
			
		return events;
		
	}
	
	/**
	 * what it makes is to obtain a event by its ID
	 * @param eventId
	 * @return event
	 * @throws DAOException
	 */
	@Override
	public Event getEvent(int eventId) throws DAOException {
		
		Event event = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = FERSDbQuery.getGetEvent();
			
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    preparedStatement.setInt(1, eventId);
		    
		    resultSet = preparedStatement.executeQuery();
		    
		    event = this.getEvent(resultSet);
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {
		    FERSDataConnection.closeStatement(preparedStatement);
		    FERSDataConnection.closeResultSet(resultSet);
		    
		}
			
		return event;
		
	}
	
	/**
	 * obtain the iD of an specific Event
	 * @param event
	 * @return id
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public int getIdEvent(Event event) throws DAOException, SQLException {

		int id = 0;
		PreparedStatement preparedStatement = null;	
		String sql = FERSDbQuery.getIdEvent();
		ResultSet rs = null;
				 				
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    preparedStatement.setString(1, event.getName());
		    preparedStatement.setString(2, event.getStartEvent());
		    
		    rs = preparedStatement.executeQuery();
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} 
		
		rs.first();
		
		id = rs.getInt("eventId");
		
		return id;
			

	}
	
	/**
	 * update all the information about an specific Event
	 * @param updateEvent
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateEvent(Event updateEvent) throws DAOException {
		
		Integer affectedRows = 0;
		PreparedStatement preparedStatement = null;	
		String sql = FERSDbQuery.getUpdateEvent();
				 				
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    preparedStatement.setString(1, updateEvent.getName());
		    preparedStatement.setString(2, updateEvent.getDescription());
		    preparedStatement.setString(3, updateEvent.getPlace());
		    preparedStatement.setString(4, updateEvent.getStartEvent());
		    preparedStatement.setString(5, updateEvent.getEndEvent());
		    preparedStatement.setString(6, updateEvent.getDayEvent());
		    preparedStatement.setString(7, updateEvent.getEventType());
		    preparedStatement.setInt(8, updateEvent.getSeatsAvailable());
		    preparedStatement.setInt(9, updateEvent.getIdPlace());
		    preparedStatement.setInt(10, updateEvent.getEventID());
		   
		    
		    affectedRows = preparedStatement.executeUpdate();
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(preparedStatement);
		    
		}
			
		return affectedRows;
		
	}
	
	/**
	 * what this method tries to perform is a insertion of an event into the database table.
	 * @param insertEvent
	 * @return insertEv
	 * @throws DAOException
	 */
	@Override
	public boolean insertEvent(Event insertEvent) throws DAOException {
		
		boolean insertEv = false;
		Integer affectedRows = 0;	
		PreparedStatement preparedStatement = null;
		String sql = FERSDbQuery.getInsertEvent();
					 			
		try {
		    preparedStatement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
		    //preparedStatement.setInt(1, insertEvent.getEventID());
		    preparedStatement.setString(1, insertEvent.getName());
		    preparedStatement.setString(2, insertEvent.getDescription());
		    preparedStatement.setString(3, insertEvent.getPlace());
		    preparedStatement.setString(4, insertEvent.getStartEvent());
		    preparedStatement.setString(5, insertEvent.getEndEvent());
		    preparedStatement.setString(6, insertEvent.getDayEvent());
		    preparedStatement.setString(7, insertEvent.getEventType());
		    preparedStatement.setInt(8, insertEvent.getSeatsAvailable());
		    preparedStatement.setInt(9, insertEvent.getIdPlace());
		    
		    affectedRows = preparedStatement.executeUpdate();
		    
		    ResultSet keys = preparedStatement.getGeneratedKeys();
			keys.next();
			key = keys.getInt(1);
			
			insertEvent.setEventID(key);
			
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(preparedStatement);
		    
		}
		if(affectedRows > 0){
			insertEv = true;
		}
			
		return insertEv;
	}
	
	/**
	 * update the availability of the event 
	 * @param event
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateSeatsEventInc(Event event) throws DAOException {
		
		Integer affectedRows = 0;
		PreparedStatement preparedStatement = null;
		String sql = FERSDbQuery.getUpdateSeatsEventInc();
					 			
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    preparedStatement.setInt(1, event.getEventID());	
		    
		    affectedRows = preparedStatement.executeUpdate();
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(preparedStatement);
		    
		}	
			
		return affectedRows;
	    }  
	
	/**
	 * update the availability of the event 
	 * @param event
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateSeatsEventDec(Event event) throws DAOException {
		
		Integer affectedRows = 0;
		PreparedStatement preparedStatement = null;
		String sql = FERSDbQuery.getUpdateSeatsEventDec();				 		
		
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    preparedStatement.setInt(1, event.getEventID());
		    
		    affectedRows = preparedStatement.executeUpdate();
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(preparedStatement);
		    
		}	
			
		return affectedRows;
	}
	
	/*public static void main (String [] args){
		Connection conn;
		EventDAOImpl eventDAOImpl2;
		Event event1;
		
		int affectedRows;
		
		conn = FERSDataConnection.getConnection();
		eventDAOImpl2 = new EventDAOImpl (conn);
		
		event1 = new Event();
		
		event1.setName("concierto Rolling");
		event1.setDescription("concierto muy bonito");
		event1.setPlace("estadio vicente calderon");
		event1.setStartEvent("12:00");
		event1.setEndEvent("18:00");
		event1.setDayEvent("viernes 27 de Febrero");
		event1.setEventType("concierto");
		event1.setSeatsAvailable(20000);
		event1.setIdPlace(1);
		
		affectedRows = eventDAOImpl2.insertEvent(event1);
		
		try{
			FERSDataConnection.commit(conn);
			FERSDataConnection.closeConnection(conn);
		}catch(DAOException ex){}
		
	}*/

}
